﻿using Dapper;
using Domain.Entity.bs;
using Domain.Entity.sys;
using Domain.Entity.zct;
using Domain.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Domain.IRepository;

namespace EFCore.Repository
{
    public class CustomerRepository:ICustomerRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        public async Task<int> AddCustomer(Customer customer)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                INSERT INTO bs_Customer (
                    cust_code, cust_name, cust_address, cust_linkman, cust_linkphone,
                    cust_email, cust_industry, cust_source, cust_depId, cust_depName,
                    cust_userId, cust_userName, cust_status, cust_accountName,
                    cust_taxpayerNum, cust_bankAccount, cust_openBank, cust_openBankAddress,
                    create_name, create_time, update_name, update_time, is_delete, remark
                ) VALUES (
                    @cust_code, @cust_name, @cust_address, @cust_linkman, @cust_linkphone,
                    @cust_email, @cust_industry, @cust_source, @cust_depId, @cust_depName,
                    @cust_userId, @cust_userName, @cust_status, @cust_accountName,
                    @cust_taxpayerNum, @cust_bankAccount, @cust_openBank, @cust_openBankAddress,
                    @create_name, GETDATE(), @update_name, GETDATE(), 0, @remark
                );";

                var parameters = new DynamicParameters();
                parameters.Add("@cust_code", customer.cust_code);
                parameters.Add("@cust_name", customer.cust_name);
                parameters.Add("@cust_address", customer.cust_address);
                parameters.Add("@cust_linkman", customer.cust_linkman);
                parameters.Add("@cust_linkphone", customer.cust_linkphone);
                parameters.Add("@cust_email", customer.cust_email);
                parameters.Add("@cust_industry", customer.cust_industry);
                parameters.Add("@cust_source", customer.cust_source);
                parameters.Add("@cust_depId", customer.cust_depId);
                parameters.Add("@cust_depName", customer.cust_depName);
                parameters.Add("@cust_userId", customer.cust_userId);
                parameters.Add("@cust_userName", customer.cust_userName);
                parameters.Add("@cust_status", customer.cust_status);
                parameters.Add("@cust_accountName", customer.cust_accountName);
                parameters.Add("@cust_taxpayerNum", customer.cust_taxpayerNum);
                parameters.Add("@cust_bankAccount", customer.cust_bankAccount);
                parameters.Add("@cust_openBank", customer.cust_openBank);
                parameters.Add("@cust_openBankAddress", customer.cust_openBankAddress);
                parameters.Add("@create_name", customer.create_name);
                parameters.Add("@update_name", customer.update_name);
                parameters.Add("@remark", customer.remark);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        //逻辑删除
        public async Task<int> DeleteCustomer(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 1. 检查客户是否存在且未被删除
                var checkExistsSql = "SELECT COUNT(1) FROM bs_customer WHERE cust_id = @id AND is_delete = 0";
                var exists = await sqlConnection.QuerySingleAsync<int>(checkExistsSql, new { id });
                if (exists == 0)
                {
                    return -1; // 客户不存在或已被逻辑删除
                }

                // 2. 检查是否有未删除的引用记录
                // 注意：根据你的表结构，字段名是 cust_id
                var checkSql = @"
                -- 检查销售退货单
                SELECT COUNT(*) FROM ord_sale_return_h WHERE cust_id = @id AND is_delete = 0
                UNION ALL
                -- 检查销售订单
                SELECT COUNT(*) FROM ord_sale_h WHERE cust_id = @id AND is_delete = 0
                UNION ALL
                -- 检查入库单 (注意：入库单通常是针对供应商，但你提到有 cust_id)
                SELECT COUNT(*) FROM in_instock_h WHERE cust_id = @id AND is_delete = 0
                UNION ALL
                -- 检查出库单
                SELECT COUNT(*) FROM out_outstock_h WHERE cust_id = @id AND is_delete = 0";

                var counts = await sqlConnection.QueryAsync<int>(checkSql, new { id });

                // 如果任何一个表有引用记录，都不能删除
                if (counts.Any(count => count > 0))
                {
                    return 0; // 有引用，删除失败
                }

                // 3. 没有引用，执行逻辑删除
                var updateSql = @"
                UPDATE bs_customer 
                SET is_delete = 1 
                WHERE cust_id = @id AND is_delete = 0"; // 再次确认未被删除，防止并发问题

                var rowsAffected = await sqlConnection.ExecuteAsync(updateSql, new { id });
                // 理论上 rowsAffected 应该是 1，但如果并发情况下已被其他请求删除，则可能是 0
                return rowsAffected > 0 ? 1 : -1; // 1=成功, -1=意外失败(如并发删除)
            }
        }

        public async Task<int> UpdateCustomer(Customer customer)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                UPDATE bs_customer
                SET 
                    cust_name = @cust_name,
                    cust_address = @cust_address,
                    cust_linkman = @cust_linkman,
                    cust_linkphone = @cust_linkphone,
                    cust_email = @cust_email,
                    cust_industry = @cust_industry,
                    cust_source = @cust_source,
                    cust_depId = @cust_depId,
                    cust_depName = @cust_depName,
                    cust_userId = @cust_userId,
                    cust_userName = @cust_userName,
                    cust_status = @cust_status,
                    cust_accountName = @cust_accountName,
                    cust_taxpayerNum = @cust_taxpayerNum,
                    cust_bankAccount = @cust_bankAccount,
                    cust_openBank = @cust_openBank,
                    cust_openBankAddress = @cust_openBankAddress,
                    update_name = @update_name,
                    update_time = GETDATE(),
                    remark = @remark
                WHERE cust_id = @cust_id;";

                var parameters = new DynamicParameters();
                parameters.Add("@cust_name", customer.cust_name);
                parameters.Add("@cust_address", customer.cust_address);
                parameters.Add("@cust_linkman", customer.cust_linkman);
                parameters.Add("@cust_linkphone", customer.cust_linkphone);
                parameters.Add("@cust_email", customer.cust_email);
                parameters.Add("@cust_industry", customer.cust_industry);
                parameters.Add("@cust_source", customer.cust_source);
                parameters.Add("@cust_depId", customer.cust_depId);
                parameters.Add("@cust_depName", customer.cust_depName);
                parameters.Add("@cust_userId", customer.cust_userId);
                parameters.Add("@cust_userName", customer.cust_userName);
                parameters.Add("@cust_status", customer.cust_status);
                parameters.Add("@cust_accountName", customer.cust_accountName);
                parameters.Add("@cust_taxpayerNum", customer.cust_taxpayerNum);
                parameters.Add("@cust_bankAccount", customer.cust_bankAccount);
                parameters.Add("@cust_openBank", customer.cust_openBank);
                parameters.Add("@cust_openBankAddress", customer.cust_openBankAddress);
                parameters.Add("@update_name", customer.update_name);
                parameters.Add("@remark", customer.remark);
                parameters.Add("@cust_id", customer.cust_id);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        //修改状态
        public async Task<int> UpdateStatus(int id, int cust_status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                UPDATE bs_customer
                SET 
                    cust_status = @cust_status
                WHERE cust_id = @cust_id;";
                if (1 == cust_status)
                    cust_status = 0;
                else
                    cust_status = 1;

                var parameters = new DynamicParameters();
                parameters.Add("@cust_status", cust_status);
                parameters.Add("@cust_id", id);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        public async Task<FenYe<Customer>> GetCustomer(FenYe<Customer> fenYe, string? cust_code, string? cust_name, int? cust_status, string? cust_industry, string? cust_userName)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                string whereBuilder = $" is_delete=0 ";
                var parameters = new DynamicParameters();

                if (!string.IsNullOrEmpty(cust_code))
                {
                    whereBuilder += $" and (cust_code like '%{cust_code}%' or cust_name like '%{cust_name}%')";
                }
                if (cust_status.HasValue)
                {
                    whereBuilder += $" and cust_status = {cust_status}";
                }
                if (!string.IsNullOrEmpty(cust_industry))
                {
                    whereBuilder += $" and cust_industry like '%{cust_industry}%'";
                }
                if (!string.IsNullOrEmpty(cust_userName))
                {
                    whereBuilder += $" and cust_userName like '%{cust_userName}%'";
                }

                // 分页参数
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "cust_id"); // 主键字段
                parameters.Add("@tabelName", "bs_Customer");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder);
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Customer>(sql, parameters);

                // 设置分页结果
                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        public async Task<List<Department>> GetDepartment(int? Department_id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                StringBuilder whereBuilder = new StringBuilder("is_delete=0 AND department_status=0");
                var parameters = new DynamicParameters();

                if (Department_id.HasValue)
                {
                    whereBuilder.Append(" AND department_id = @Department_id");
                    parameters.Add("@Department_id", Department_id.Value);
                }

                string sql = $"SELECT * FROM zct_department WHERE {whereBuilder.ToString()}";
                return (await sqlConnection.QueryAsync<Department>(sql, parameters)).AsList();
            }
        }

        public async Task<List<User>> GetUser(int? Department_id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                StringBuilder whereBuilder = new StringBuilder("is_delete=0 AND user_status=0");
                var parameters = new DynamicParameters();

                if (Department_id.HasValue)
                {
                    whereBuilder.Append(" AND department_id = @Department_id");
                    parameters.Add("@Department_id", Department_id.Value);
                }

                string sql = $"SELECT * FROM zct_user WHERE {whereBuilder.ToString()}";
                return (await sqlConnection.QueryAsync<User>(sql, parameters)).AsList();
            }
        }

        public async Task<List<DictData>> GetDictData(int dict_typename)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询语句，直接包含必要的条件
                string sql = "SELECT * FROM sys_dict_data WHERE is_delete = 0 AND status = 0 AND dict_id = @dict_typename";

                // 创建动态参数
                var parameters = new DynamicParameters();
                parameters.Add("@dict_typename", dict_typename);

                // 执行查询并返回结果集合
                return (await sqlConnection.QueryAsync<DictData>(sql, parameters)).AsList();
            }
        }

        public async Task<Customer> GetIdCustomer(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询语句，直接包含必要的条件
                string sql = "SELECT * FROM bs_customer WHERE  cust_id = @id";

                // 创建动态参数
                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                // 执行查询并返回结果集合
                return await sqlConnection.QueryFirstOrDefaultAsync<Customer>(sql, parameters);
            }
        }

        //高级查询
        public async Task<FenYe<Customer>> GetSeniorCustomer(FenYe<Customer> fenYe, string? cust_code, string? cust_name, string? cust_industry, string? cust_source, string? cust_linkman, string? cust_linkphone, string? cust_email, string? cust_depName, string? cust_userName, int? cust_status, string? cust_address, string? cust_accountName, string? cust_taxpayerNum, string? cust_bankAccount, string? cust_openBank, string? create_name, string? startTime, string? endTime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建基础查询条件
                StringBuilder whereBuilder = new StringBuilder("is_delete=0");

                // 转义所有字符串参数（防止SQL注入）
                if (!string.IsNullOrEmpty(cust_code))
                    whereBuilder.Append($" AND cust_code LIKE '%{EscapeSql(cust_code)}%'");

                if (!string.IsNullOrEmpty(cust_name))
                    whereBuilder.Append($" AND cust_name LIKE '%{EscapeSql(cust_name)}%'");

                if (!string.IsNullOrEmpty(cust_industry))
                    whereBuilder.Append($" AND cust_industry LIKE '%{EscapeSql(cust_industry)}%'");

                if (!string.IsNullOrEmpty(cust_source))
                    whereBuilder.Append($" AND cust_source LIKE '%{EscapeSql(cust_source)}%'");

                if (!string.IsNullOrEmpty(cust_linkman))
                    whereBuilder.Append($" AND cust_linkman LIKE '%{EscapeSql(cust_linkman)}%'");

                if (!string.IsNullOrEmpty(cust_linkphone))
                    whereBuilder.Append($" AND cust_linkphone LIKE '%{EscapeSql(cust_linkphone)}%'");

                if (!string.IsNullOrEmpty(cust_email))
                    whereBuilder.Append($" AND cust_email LIKE '%{EscapeSql(cust_email)}%'");

                if (!string.IsNullOrEmpty(cust_address))
                    whereBuilder.Append($" AND cust_address LIKE '%{EscapeSql(cust_address)}%'");

                if (!string.IsNullOrEmpty(cust_accountName))
                    whereBuilder.Append($" AND cust_accountName LIKE '%{EscapeSql(cust_accountName)}%'");

                if (!string.IsNullOrEmpty(cust_taxpayerNum))
                    whereBuilder.Append($" AND cust_taxpayerNum LIKE '%{EscapeSql(cust_taxpayerNum)}%'");

                if (!string.IsNullOrEmpty(cust_bankAccount))
                    whereBuilder.Append($" AND cust_bankAccount LIKE '%{EscapeSql(cust_bankAccount)}%'");

                if (!string.IsNullOrEmpty(cust_openBank))
                    whereBuilder.Append($" AND cust_openBank LIKE '%{EscapeSql(cust_openBank)}%'");

                if (!string.IsNullOrEmpty(create_name))
                    whereBuilder.Append($" AND create_name LIKE '%{EscapeSql(create_name)}%'");

                // 处理整型参数
                if (!string.IsNullOrEmpty(cust_depName))
                    whereBuilder.Append($" AND cust_depName LIKE '%{EscapeSql(cust_depName)}%'");

                if (!string.IsNullOrEmpty(cust_userName))
                    whereBuilder.Append($" AND cust_userName LIKE '%{EscapeSql(cust_userName)}%'");


                if (cust_status.HasValue)
                {
                    // 如果 cust_status 是 0 或 1，则添加相应的查询条件
                    whereBuilder.Append($" AND cust_status = {cust_status}");
                }

                // 处理创建时间范围
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    // 转义并清理输入
                    string start = EscapeSql(startTime.Trim());
                    string end = EscapeSql(endTime.Trim());

                    // 确保日期格式正确（取前10个字符作为日期部分）
                    string startDate = start.Length >= 10 ? start.Substring(0, 10) : start;
                    string endDate = end.Length >= 10 ? end.Substring(0, 10) : end;

                    // 构建包含全天的时间范围
                    whereBuilder.Append($" AND create_time BETWEEN '{startDate} 00:00:00' AND '{endDate} 23:59:59'");
                }

                // 分页参数
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", 10); // 默认10条
                parameters.Add("@keyName", "cust_id");
                parameters.Add("@tabelName", "bs_Customer");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder.ToString());
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Customer>(sql, parameters);

                // 设置分页结果
                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        // SQL 注入防护：转义特殊字符
        private string EscapeSql(string value)
        {
            return value
                .Replace("'", "''")    // 转义单引号
                .Replace("--", "")      // 移除SQL注释
                .Replace(";", "");      // 移除语句分隔符
        }

        public async Task<List<string>> GetCustomerID()
        {
            await using (var sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                const string query = "SELECT cust_code FROM bs_customer WHERE cust_code IS NOT NULL AND is_delete = 0";

                var result = (await sqlConnection.QueryAsync<string>(query)).AsList();
                return result;
            }
        }


        //查询所有客户
        public async Task<List<Customer>> GetCustomers()
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select cust_id,cust_name from [dbo].[bs_customer] where is_delete=0";
                return sqlConnection.Query<Customer>(sql).ToList();
            }
        }
    }
}
